﻿/*
 * DbFileExplorer
 *
 * Copyright (c) 2012, Stephane Cuillerdier
 * All rights reserved.
 * 
 * This software is licensed under the new BSD License:
 * 
 * Redistribution and use in source and binary forms, with or without 
 * modification, are permitted provided that the following conditions are met:
 *
 * Redistributions of source code must retain the above copyright notice, 
 * this list of conditions and the following disclaimer.
 * Redistributions in binary form must reproduce the above copyright notice, 
 * this list of conditions and the following disclaimer in the documentation 
 * and/or other materials provided with the distribution.
 * 
 * Neither the name of the author nor the names of its contributors may 
 * be used to endorse or promote products derived from this software without 
 * specific prior written permission.
 *
 * THIS SOFTWARE IS PROVIDED BY THE COPYRIGHT HOLDERS AND CONTRIBUTORS "AS IS" 
 * AND ANY EXPRESS OR IMPLIED WARRANTIES, INCLUDING, BUT NOT LIMITED TO, THE 
 * IMPLIED WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE 
 * ARE DISCLAIMED. IN NO EVENT SHALL THE COPYRIGHT OWNER OR CONTRIBUTORS BE 
 * LIABLE FOR ANY DIRECT, INDIRECT, INCIDENTAL, SPECIAL, EXEMPLARY, OR 
 * CONSEQUENTIAL DAMAGES (INCLUDING, BUT NOT LIMITED TO, PROCUREMENT OF 
 * SUBSTITUTE GOODS OR SERVICES; LOSS OF USE, DATA, OR PROFITS; OR BUSINESS 
 * INTERRUPTION) HOWEVER CAUSED AND ON ANY THEORY OF LIABILITY, WHETHER IN 
 * CONTRACT, STRICT LIABILITY, OR TORT (INCLUDING NEGLIGENCE OR OTHERWISE) 
 * ARISING IN ANY WAY OUT OF THE USE OF THIS SOFTWARE, EVEN IF ADVISED OF THE 
 * POSSIBILITY OF SUCH DAMAGE.
 * 
 * version 1.0
 */
using System;
using System.Data;
using System.Collections.Generic;
using System.Collections.ObjectModel;
using System.Text;
using System.Drawing;
using System.Windows.Forms;
using XPTable.Models;
using XPTable.Renderers;
using System.IO;
using System.Globalization;
using Gajatko.IniFiles;
using System.Linq;

namespace DBFileExplorer
{
	/// <summary>
	/// Description of MainForm.
	/// </summary>
	public partial class MainForm : Form
	{
		#region declaration
		
		public int m_iColumnCount;
		public DataTable m_dtCSV;
        private string ConfigForFilteringFileName;
        private string ConfigForOptionFileName;
        public String[] HeaderArray;
        private string fileName;
        private int excelRow;
        private int excelCol;
        private int TreeClassementProfondeur; // le level du dernier niveau d'enfant
		public OptionsPropertyGridClass OptionsParam;
		
        #endregion
        
        #region initialisation
        
        public MainForm()
		{
			InitializeComponent();
            
            ConfigForFilteringFileName = "FilteringConfig.ini";
            ConfigForOptionFileName = "OptionConfig.ini";
            
            this.triPane.SetMainFormPointer(this);
            
            //XPTable1.HeaderRenderer = new XPTable.Renderers.FlatHeaderRenderer();
            //XPTable1.HeaderRenderer = new XPTable.Renderers.GradientHeaderRenderer();
            //XPTable1.HeaderRenderer = new XPTable.Renderers.XPHeaderRenderer();
            
            // Populate
            dataGridView.Columns.Clear();
			//this.XPTableColumnModel.Columns.Clear();
			this.triPane.listCriteresTri.Items.Clear();
			//this.triPane.listCriteresTri.ItemCheck += new System.Windows.Forms.ItemCheckEventHandler(this.ListCriteresTriItemCheck);
			
			TreeClassementProfondeur = 0;
			
			OptionsParam = new OptionsPropertyGridClass();
			
			LoadLastConfigForOptions();
		}
		
        void MainFormFormClosing(object sender, FormClosingEventArgs e)
		{
			SaveConfigForFiltering();
			SaveConfigForOptions();
        }
        
        #endregion
        
        #region Load / Save Config File
        
        #region Config for Options
        
        public int LoadLastConfigForOptions()
        {    
        	if ( !File.Exists(ConfigForOptionFileName) )
        	{
        	    DefaultValues();
        	}
        	else
        	{
        		IniFile configFile = IniFile.FromFile(ConfigForOptionFileName);
        	
        		// Options
				OptionsParam.Separateur_de_champ = configFile["Options"]["Separateur_de_champ"];
				if ( configFile["Options"]["Associateur_de_champ"] != null )
					OptionsParam.Associateur_de_champ = configFile["Options"]["Associateur_de_champ"][0];
					
				// Default Values
				if ( configFile["Options"]["Associateur_de_champ"] == null ) 
					OptionsParam.Associateur_de_champ = '+';
				
				configFile = null;
        	}
			return 0;
        }
        
        public int SaveConfigForOptions()
        {
        	IniFile configFile = new IniFile();
			
        	// Options
			configFile["Options"]["Separateur_de_champ"] = OptionsParam.Separateur_de_champ;
			configFile["Options"]["Associateur_de_champ"] = OptionsParam.Associateur_de_champ.ToString();
			
            configFile.Save(ConfigForOptionFileName);
            configFile = null;
            
            return 0;
        }
        
        public int DefaultValues()
        {
        	OptionsParam.Separateur_de_champ = " - ";
        	OptionsParam.Associateur_de_champ = '+';
        	
        	return 0;
        }
        #endregion
        
        #region Config for Filtering
        
        public int LoadLastConfigForFiltering()
		{
        	IniFile configFile = IniFile.FromFile(ConfigForFilteringFileName);
        	
        	if ( m_dtCSV != null )
			{
        		// -----------------------------------------------------------------------
	        	// Fill Tree and List of TriCriterePane	
				// -----------------------------------------------------------------------
	        	int indexlvl = 0;
				string nodelvl = "Node" + indexlvl.ToString();
				TreeNode nodeParent = null;
				while( configFile["CritereTree"][nodelvl] != null )
				{
					string nodeName = configFile["CritereTree"][nodelvl];
					
					// on efface le ou les items de la liste ( mode fusion )
					int idItem = 0;
					if ( nodeName.Contains(",") ) // ModeFusion
	                {
	                	// il s'agit de separer chaque item séparé par le +
	                	string[] tabName = nodeName.Split(',');
	                	string tagStr = "";
	                	int idx = 0;
	                	string newNodeName = "";
	                	foreach( string str in tabName )
	                	{
							if ( triPane.listCriteresTri.Items[str] != null )
							{
								idItem = Convert.ToInt32(triPane.listCriteresTri.Items[str].Tag);
								triPane.listCriteresTri.Items[str].Remove();
							}
							// on cree le tag composé
							if ( idx == 0 ) tagStr += Convert.ToString(idItem);
							else tagStr += this.OptionsParam.Associateur_de_champ + Convert.ToString(idItem);
							newNodeName += str + this.OptionsParam.Associateur_de_champ;
							idx++;
	                	}
	                	nodeName = newNodeName.Remove(newNodeName.Length-1,1);
	                	// on l'ajoute à l'arbo
						if ( nodeParent == null  )
						{
							nodeParent = triPane.treeCriteres.Nodes.Add(nodeName, nodeName);
							nodeParent.Tag = tagStr;
						}
						else
						{
							nodeParent = nodeParent.Nodes.Add(nodeName, nodeName);
							nodeParent.Tag = tagStr;
						}
	                }
					else
					{
						if ( triPane.listCriteresTri.Items[nodeName] != null )
						{
							idItem = (int)triPane.listCriteresTri.Items[nodeName].Tag;
							triPane.listCriteresTri.Items[nodeName].Remove();
						}
						// on l'ajoute à l'arbo
						if ( nodeParent == null  )
						{
							nodeParent = triPane.treeCriteres.Nodes.Add(nodeName, nodeName);
							nodeParent.Tag = idItem;
						}
						else
						{
							nodeParent = nodeParent.Nodes.Add(nodeName, nodeName);
							nodeParent.Tag = idItem;
						}
					}
					indexlvl++;
					nodelvl = "Node" + indexlvl.ToString();
				}
				triPane.treeCriteres.ExpandAll();
				
				// Critere list
				foreach( string it in configFile["CritereList"].GetKeys() )
				{
					if ( configFile["CritereList"][it] == "1" ) 
						this.triPane.listCriteresTri.Items[it].Checked = true;
				}
        	}
			
			configFile = null;
			
			return 0;
		}

		public int SaveConfigForFiltering()
		{
			if ( m_dtCSV != null )
			{
				IniFile configFile = new IniFile();
				
				char champAssocChr = this.OptionsParam.Associateur_de_champ;
				string champAssocStr = champAssocChr.ToString();
				
				// -----------------------------------------------------------------------
	        	// Fill Tree and List of TriCriterePane	
				// -----------------------------------------------------------------------
	        	TreeNode node = null;
	            if ( triPane.treeCriteres.Nodes.Count > 0 ) node = triPane.treeCriteres.Nodes[0];
	            int indexlvl = 0;
	            while ( node != null )
	            {
	            	string nodelvl = "Node" + indexlvl.ToString();
	            	string nodeName = node.Text;
	            	// transforme mot+mot en mot,mot
	           		string newNodeName = "";
	            	string[] mots = nodeName.Split(champAssocChr);
	            	foreach( string mot in mots)
	            	{
	            		newNodeName += mot + ",";
	            	}
	            	nodeName = newNodeName.Remove(newNodeName.Length-1,1);
	            	// Fin de la transformation
	            	configFile["CritereTree"][nodelvl] = nodeName;
					if ( node.Nodes.Count > 0 ) node = node.Nodes[0];
					else node = null;
	            	indexlvl++;
	            }
	            
	            // On sauvegrarde les items checkés
	            //enleve les colonne checkées dnas la liste des criteres de l'affichage final
				foreach( ListViewItem it in this.triPane.listCriteresTri.Items )
				{
					if ( it.Checked == true ) configFile["CritereList"][it.Name] = "1";
				}
				
				configFile.Save(ConfigForFilteringFileName);
            	configFile = null;
			}
						
            return 0;
            
		}
		
		#endregion
        
		#endregion
        
        #region XPtable
        
        // si display_or_return = true => alors la frocntion affiche le resultat dans la xpTable sinon elle retorune la Datatable
		public DataTable PopulateXPTableWithTreeClassementByTreeNode(TreeNode SelectedNode, bool DisplayOrReturnDataTable)
		{
			char champAssocChr = this.OptionsParam.Associateur_de_champ;
			string champAssocStr = champAssocChr.ToString();
			string champSepStr = this.OptionsParam.Separateur_de_champ;
			
			//dataGridView
			string where_query = "";
			// -----------------------------------------------------------------------------------------------------
			// on va recupere l'itemTreeNode selectionné et les parent qui sont impacté pour formater un requete
			// -----------------------------------------------------------------------------------------------------
			// "select ch1, ch2, ch3 from tbl_fdb where fdb_Projet = 'X07' and fdb_Metier = 'SMO' ";
			// -----------------------------------------------------------------------------------------------------
			DataTable dtClone = m_dtCSV.Copy();
			DataTable tbl = null;
			TreeNode node = null;
			if ( SelectedNode != null )
			{
				string nodeTag = Convert.ToString(SelectedNode.Tag);
				if ( nodeTag != "ROOT" ) // selectionner root montre le tableau sans filtre mais avec les colonnes a cacher
				{
					node = SelectedNode;
					while ( node != null )
					{
						string nodeChamp = node.Tag.ToString().ToUpper();
						if ( nodeChamp != "ROOT" )
						{
							if ( node != SelectedNode ) where_query += " AND ";
							string nodeText = node.Text.Replace("'","");
							if ( nodeChamp.Contains(this.OptionsParam.Associateur_de_champ) ) // le tag contient des + il va donc falloir extraire uniquement le mot criterisant pour la suite 
							{
								string[] s = nodeChamp.Split(this.OptionsParam.Associateur_de_champ);
								nodeChamp = s[0]; // le mot criterisant est le premier
								string[] s2 = nodeChamp.Split(','); // mot du type string,longueur de la string pour pouvoir decouper convenablement nodeText pour la requete qui suit
								int len = Convert.ToInt32(s2[1]);
								nodeChamp = s2[0];
								nodeText = nodeText.Substring(0,len); // len pour recuperer que le mot criterisant
							}
							where_query += nodeChamp + " = '" + nodeText + "'";
						}
						node = node.Parent;
					}
					// -----------------------------------------------------------------------------------------------------
			        DataRow[] results = dtClone.Select(where_query);
			        tbl = new DataTable();
			        foreach( string str in HeaderArray)
			        {
			        	tbl.Columns.Add(str);
			        }
			        foreach( DataRow row in results)
			        {
			        	DataRow roww = tbl.NewRow();
			        	foreach( string str in HeaderArray)
				        {
			        		roww[str] = row[str];
				        }
			        	tbl.Rows.Add(roww);
			        }
		        }
				else
					tbl = m_dtCSV.Copy();
			
				
		        // enleve les colonnes utilisées par le filtre ( l'arborescence )
		        node = SelectedNode;
				while ( node != null )
				{
					nodeTag = node.Tag.ToString();
					if ( nodeTag != "ROOT" )
					{
						if ( nodeTag.Contains(this.OptionsParam.Associateur_de_champ)  )
						{
							string[] tags = nodeTag.Split(this.OptionsParam.Associateur_de_champ);
							foreach(string tag in tags)
							{
								string[] s = tag.Split(',');
								tbl.Columns.Remove(s[0]);
							}
						}
						else
							tbl.Columns.Remove(nodeTag);
					}
					node = node.Parent;
				}
				
	        }
			else
			{
				tbl = m_dtCSV.Copy();
			}
			
			//enleve les colonne checkées dnas la liste des criteres de l'affichage final
			foreach( ListViewItem lit in this.triPane.listCriteresTri.Items )
			{
				if ( lit != null )
					if ( lit.Checked == true ) 
						tbl.Columns.Remove(lit.Name);
			}
			
			// et re select_distinct sur les donnée avec tout les champs pour pas avoir des lignes complete en doublons
			string[] lChamp = new String[tbl.Columns.Count];
			int idx=0;
			foreach( DataColumn dc in tbl.Columns)
			{
				lChamp[idx++] = dc.ToString();
			}
			DataTable tbl_Filtered = SelectDistinct(tbl, lChamp);
			
			if ( DisplayOrReturnDataTable == true )
				ShowDataTable(tbl_Filtered);
			else
	        	return tbl_Filtered;
			
			tbl = null;
			
			return null;
		}
		
		private void ShowDataTable(DataTable tableToShow)
		{
			dataGridView.DataSource = tableToShow.DefaultView;
			dataGridView.Update();
			
			//XPTable1.DataSource = tableToShow.DefaultView;
			//XPTable1.Update();
			//XPTable1.AutoResizeColumnWidths();
		}
		
		#endregion
		
		#region Drag & Drop Event
		
		void ListView1DragEnter(object sender, DragEventArgs e)
		{
			
		}
		
		void ListView1DragLeave(object sender, EventArgs e)
		{
			
		}
		
		void ListView1DragOver(object sender, DragEventArgs e)
		{
			
		}
		
		void TreeView2DragDrop(object sender, DragEventArgs e)
		{
			
		}
		
		void TreeView2DragEnter(object sender, DragEventArgs e)
		{
			
		}
		
		void TreeView2DragOver(object sender, DragEventArgs e)
		{
			
		}
		
		void XPTable1DragEnter(object sender, DragEventArgs e)
		{
			
		}
		
		void XPTable1DragDrop(object sender, DragEventArgs e)
		{
			
		}
		
		void TreeClassementDragDrop(object sender, DragEventArgs e)
		{
			
		}
		
		void TreeClassementDragEnter(object sender, DragEventArgs e)
		{
			
		}
		
		void TreeClassementItemDrag(object sender, ItemDragEventArgs e)
		{
			
		}
		
		void XPTable1DragOver(object sender, DragEventArgs e)
		{
			
		}
		
		#endregion
			
        #region Import CSV in DataTable
        
        private void PopulateDataTableFromUploadedFile(System.IO.Stream strm)
		{
        	// on desactive pour pas que quand on va cliquer le tabpage 2 d'un coup la pile d'evenement s'execute et fasse ramer la table
        	this.triPane.listCriteresTri.ItemCheck -= new System.Windows.Forms.ItemCheckEventHandler( this.ListCriteresTriItemCheck );
        	
        	System.IO.StreamReader srdr = new System.IO.StreamReader(strm);
		    String strLine = String.Empty;
		    Int32 iLineCount = 0;
		    do
		    {
		        strLine = srdr.ReadLine();
		        if (strLine == null)
		        {
		            break;
		        }
		        if (0 == iLineCount++)
		        {
		            m_dtCSV = this.CreateDataTableForCSVData(strLine); // Entete
		        }
		        if ( iLineCount > 1 )
		        {
		        	this.AddDataRowToTable(strLine, m_dtCSV); // Les lignes
		        }
		    } 
		    while (true);
		    
		    LoadLastConfigForFiltering();
		    MajTreeClassement();
		    ShowDataTable(m_dtCSV);
		    
		    this.triPane.listCriteresTri.ItemCheck += new System.Windows.Forms.ItemCheckEventHandler(this.ListCriteresTriItemCheck);
		}       
		
		private DataTable CreateDataTableForCSVData(String strLine)
		{
		    DataTable dt = new DataTable("CSVTable");
		    HeaderArray = strLine.Split(new char[] { ';' });
		    m_iColumnCount = HeaderArray.Length;
		    foreach(String strVal in HeaderArray)
		    {
		    	String strVal2 = strVal.Replace(' ', '_');
		    	// si le header existe déja on lui ajoute un nombre incrémenté
		    	String lastStr = "";
		    	if ( dt.Columns.Contains(strVal2) == true ) 
		    	{
		    		foreach(DataColumn dc in dt.Columns)
		    		{
		    			if ( dc.Caption.Contains(strVal2) ) 
		    				lastStr = dc.Caption;

		    		}
			    	String[] sp = lastStr.Split('_');
			    	int idx = sp.Count() - 1;
			    	string ss = sp[idx].ToString();
			    	int num = 0;
			    	try 
			    	{ 
			    		num = Convert.ToInt32(ss); 
			    		lastStr = "";
			    		int countSP = sp.Count()-1;
			    		for (int i=0; i<countSP; i++)
			    		{
			    			lastStr += sp[i] + "_";	    			
			    		}

			    		lastStr += (num+1).ToString();
			    	}
			    	catch 
			    	{ 
			    		lastStr += "_" + (1).ToString();			    	
			    	}
		    	}
		    	if ( lastStr == "" ) lastStr = strVal2;
		    	dt.Columns.Add(lastStr, Type.GetType("System.String"));
		        int id = triPane.listCriteresTri.Items.Add(lastStr, lastStr, "").Index;
				triPane.listCriteresTri.Items[lastStr].Tag = id;
		    }
		    return dt;
		}
		
		private DataRow AddDataRowToTable(String strCSVLine, DataTable dt)
		{
		    String[] strVals = strCSVLine.Split(new char[] { ';' });
		    Int32 iTotalNumberOfValues = strVals.Length;
		    // If number of values in this line are more than the columns
		    // currently in table, then we need to add more columns to table.
		    if (iTotalNumberOfValues > m_iColumnCount)
		    {
		        Int32 iDiff = iTotalNumberOfValues - m_iColumnCount;
        		for(Int32 i = 0; i < iDiff; i++)
		        {
		            String strColumnName = String.Format("Column-{0}", (m_iColumnCount + i));
		            dt.Columns.Add(strColumnName, Type.GetType("System.String"));
		        }
		        m_iColumnCount = iTotalNumberOfValues;
		    }
		    int idx = 0;
		    DataRow drow = dt.NewRow();
		    foreach (String strVal in strVals)
		    {
		    	String strColumnName = HeaderArray.GetValue(idx++).ToString();
		    	strColumnName = strColumnName.Replace(' ', '_');
		    	drow[strColumnName] = strVal.Trim().Replace("'","");
		    }
		    dt.Rows.Add(drow);
		    return drow;
		}                     
        
		void OpenCSVFileBtnClick(object sender, EventArgs e)
		{
			Stream myStream;
    
			OpenFileDialog openCSVFileDlg = new OpenFileDialog();

		    //openCSVFileDlg.InitialDirectory = "c:\\" ;
		    openCSVFileDlg.Filter = "CSV files (*.csv)|*.csv|All files (*.*)|*.*" ;
		    openCSVFileDlg.FilterIndex = 2 ;
		    openCSVFileDlg.RestoreDirectory = true ;
		
		    if(openCSVFileDlg.ShowDialog() == DialogResult.OK)
		    {
		        if((myStream = openCSVFileDlg.OpenFile())!= null)
		        {
		        	fileName = openCSVFileDlg.FileName;
		        	PopulateDataTableFromUploadedFile(myStream);
		        	
		        	myStream.Close();
		        }
		    }
		}
		
		#endregion
		
		#region Select Distinct In DataTable

		private static DataTable SelectDistinct(DataTable SourceTable, params string[] FieldNames)
		{
		     object[] lastValues;
		     DataTable newTable;
		     DataRow[] orderedRows;
		
		     if (FieldNames == null || FieldNames.Length == 0)
		          throw new ArgumentNullException("FieldNames");
		
		     lastValues = new object[FieldNames.Length];
		     newTable = new DataTable();
		
		     foreach (string fieldName in FieldNames)
		     	if ( SourceTable.Columns[fieldName] != null )
		          newTable.Columns.Add(fieldName, SourceTable.Columns[fieldName].DataType);

		     orderedRows = SourceTable.Select("", string.Join(", ", FieldNames));
		
		     foreach (DataRow row in orderedRows)
		     {
		          if (!fieldValuesAreEqual(lastValues, row, FieldNames))
		          {
		               newTable.Rows.Add(createRowClone(row, newTable.NewRow(), FieldNames));
		
		               setLastValues(lastValues, row, FieldNames);
		          }
		     }
		
		     return newTable;
		}
		
		private static bool fieldValuesAreEqual(object[] lastValues, DataRow currentRow, string[] fieldNames)
		{
		     bool areEqual = true;
		
		     for (int i = 0; i < fieldNames.Length; i++)
		     {
		          if (lastValues[i] == null || !lastValues[i].Equals(currentRow[fieldNames[i]]))
		          {
		               areEqual = false;
		               break;
		          }
		     }
		
		     return areEqual;
		}
		
		private static DataRow createRowClone(DataRow sourceRow, DataRow newRow, string[] fieldNames)
		{
		     foreach (string field in fieldNames)
		     	if ( field != "" )
		     		newRow[field] = sourceRow[field];
		
		     return newRow;
		}
		
		private static void setLastValues(object[] lastValues, DataRow sourceRow, string[] fieldNames)
		{
		     for (int i = 0; i < fieldNames.Length; i++)
		     	if ( fieldNames[i] != "" )
		          lastValues[i] = sourceRow[fieldNames[i]];
		}

		#endregion
		
		#region TreeClassement
		
		private List<string> listRecursChamp;
		public int MajTreeClassement()
		{
			char champAssocChr = this.OptionsParam.Associateur_de_champ;
			string champAssocStr = champAssocChr.ToString();
				
			treeClassement.Nodes.Clear();
			
			// Noeud racine
			TreeNode nRoot = treeClassement.Nodes.Add("Root");
			nRoot.Tag = "ROOT";
			
			listRecursChamp = null;
			listRecursChamp = new List<string>();
			
			// on va explorer le triPane.treeCriteres de maniere recursive et on stocke les champ dans une liste
			TreeNodeCollection nodes = triPane.treeCriteres.Nodes;
		   	foreach (TreeNode n in nodes)
		   	{
		      	PrintRecursive(n);
		   	}
		   	
		   	// Puis on construit une requete de type select avec autant de colonne qu'il n'y a de noeud trouvé précédemment
		   	if ( listRecursChamp.Count > 0 )
		   	{
		   		string select_query = "";
		        for (int j=0; j<listRecursChamp.Count; j++)
		        {
				   	if ( j > 0 ) select_query += ",";
				   	
				   	if ( listRecursChamp[j].Contains(champAssocStr) ) // node fusionné ( Mode Fusion )
				   	{
				   		string[] strSplit = listRecursChamp[j].Split(champAssocChr);
				   		foreach( string str in strSplit )
				   		{
				   			if ( HeaderArray.Contains(str) )
				   				select_query += str + ",";
				   		}
				   		select_query = select_query.Remove(select_query.Count()-1,1); // on enleve la derniere virgule
				   	}
				   	else
				   	{
				   		if ( HeaderArray.Contains(listRecursChamp[j]) )
				   			select_query += listRecursChamp[j];
				   	}
		        }
		        string[] queryListChamp = select_query.Split(','); // peut etre un poil compliqué comme méthode ( reste de requete sql de la version 0.1 )
		        // puis on l'execute
		        DataTable dTbl = SelectDistinct(m_dtCSV, queryListChamp);
		        //XPTable1.DataSource = dTbl.DefaultView;

	        	for (int j=1;j<dTbl.Rows.Count;j++) // next row // j=1 car a j=0 la ligne contient des conneries c'est tres bizarre ca ressemble a du BUFFER OVERFLOW !!!!!!!
			    {
	        		DataRow currentRow = dTbl.Rows[j];
			    	TreeNode node = null;
			    	for (int k=0;k<dTbl.Columns.Count;k++) //next field
			    	{
			    		// on va creer la ligne pour garder la coherence tout en testant l'existence d'un noued avec ce nom à chaque étape
			    		string nodeName = currentRow[k].ToString();
			    		string colName = dTbl.Columns[k].ToString();
			    		
			    		string str = listRecursChamp.Find( delegate(string s) { return s.Contains(colName); } );
			    		string nodeText = "";
			    		string nodeTag = "";
			    		if ( str.Contains(champAssocStr) )
			    		{
			    			string[] mots = str.Split(champAssocChr);
			    			if ( colName == mots[0] ) // colName est au debut de la phrase donc c'est un mot criterisant sinon il serai au mileu ou a la fin	
			    			{
			    				foreach(string mot in mots)
			    				{
			    					string name = currentRow[mot].ToString();
				    				nodeText += name + this.OptionsParam.Separateur_de_champ;
				    				nodeTag += mot + "," + name.Length + this.OptionsParam.Associateur_de_champ; // on stocke la longueur du premeir mot pour les decoupahe de texte dans PopulateXPTableWithTreeClassement
				    			}
			    				nodeTag = nodeTag.Remove(nodeTag.Count()-1,1); // on enleve le dernier +
			    			}
				    		else // pour afficahge => non crioterisnat => on passe notre chemin
				    		{
				    			nodeName = ""; // ca nous permet de ne pas passer dnas la boucle de creation des noeuds.
				    		}
			    		}
			    		else
			    		{
			    			nodeText = nodeName;
			    			nodeTag = str;
			    		}
			    		
			    		if ( nodeName != "" )
			    		{	
			    			if (k==0)
				    		{
				    			// pas de parent
				    			if ( nRoot.Nodes.ContainsKey(nodeText) ) // un noeud avec ce nom existe déja
				    			{				    				
				    				node = nRoot.Nodes[nodeText];
				    			}
				    			else
				    			{
			    					node = nRoot.Nodes.Add(nodeText, nodeText);
				    				node.Tag = nodeTag;
				    			}
				    		}
				    		else
				    		{
				    			//probablement des parent
				    			if ( node != null )
				    			{
					    			if ( node.Nodes.ContainsKey(nodeText) ) // un noeud avec ce nom existe déja
					    			{
					    				node = node.Nodes[nodeText];
					    			}
				    				else
				    				{
			    						node = node.Nodes.Add(nodeText, nodeText);
				    					node.Tag = nodeTag;
				    				}
				    			}
				    		}
			    		}
			    	}
				}
				treeClassement.ExpandAll();
			}
					
			return 0;
		}
		
		private void PrintRecursive(TreeNode treeNode)
		{
		   // Print the node.
		   listRecursChamp.Add(treeNode.Text);
		   //listRecursChamp.
		  	// Print each node recursively.
		   foreach (TreeNode tn in treeNode.Nodes)
		   {
		   	if ( tn.Level > TreeClassementProfondeur ) TreeClassementProfondeur = tn.Level;
		      PrintRecursive(tn);
		   }
		}
		
		void RafraichirToolStripMenuItemClick(object sender, EventArgs e)
		{
			MajTreeClassement();
			// on va remonter jusqu'au noeud le plus haut
			TreeNode node = treeClassement.SelectedNode;
			if ( m_dtCSV != null ) PopulateXPTableWithTreeClassementByTreeNode(node, true);
		}
		
		void TreeClassementAfterSelect(object sender, TreeViewEventArgs e)
		{
			// on va remonter jusqu'au noeud le plus haut
			TreeNode node = treeClassement.SelectedNode;
			DebugTagLabel.Text = Convert.ToString(node.Tag);
			if ( m_dtCSV != null ) PopulateXPTableWithTreeClassementByTreeNode(node, true);
		}
		
		void ToolStripButton1Click(object sender, EventArgs e)
		{
			MajTreeClassement();
			// on va remonter jusqu'au noeud le plus haut
			TreeNode node = treeClassement.SelectedNode;
			if ( m_dtCSV != null ) PopulateXPTableWithTreeClassementByTreeNode(node, true);
		}
				
		#endregion
		
		#region Divers Interface
		
		void MainFormResize(object sender, EventArgs e)
		{
			//XPTable1.AutoResizeColumnWidths();
		}
		
		void ToolStripButton2Click(object sender, EventArgs e)
		{
			AboutDlg dlg = new AboutDlg();
			dlg.ShowDialog();
		}
		
		#endregion
		
		#region Critere Pane
		
	
		void ModeFusionBtnClick(object sender, EventArgs e)
		{
			this.triPane.ModeFusion = modeFusionBtn.Checked;
		}
				
				
		void ListCriteresTriItemCheck(object sender, ItemCheckEventArgs e)
		{
			// on va remonter jusqu'au noeud le plus haut
			//TreeNode node = treeClassement.SelectedNode;
			//if ( m_dtCSV != null ) PopulateXPTableWithTreeClassementByTreeNode(node, true);
		}
				
		#endregion
				
		#region Excel Exporter
		
		private void ExportToExcel()
		{
			if ( m_dtCSV != null )
			{
				XLSFormater xlser = new XLSFormater();
				excelCol = 0;
				excelRow = 0;
				// On ouvre excel
				xlser.OpenExcelApp();
				xlser.AddSheet();//fileName);
				TreeNode node = treeClassement.Nodes[0];
				PutNodeToExcelInRecursive(node, xlser,0);
				// on va retailler toutes les colonnes
				DataTable tbl = null;
				if ( m_dtCSV != null ) 
					tbl = PopulateXPTableWithTreeClassementByTreeNode(node, false);
				int idx = TreeClassementProfondeur+1;
				foreach( DataColumn col in tbl.Columns)
				{
					xlser.AutoFitCols(idx,idx);
					idx++;
				}
				xlser = null;
			}
		}
		
		private void PutNodeToExcelInRecursive(TreeNode treeNode, XLSFormater xlsFormater, int lastLevel)
		{
			// on indente une ligne dans excel
			if ( treeNode.Text != "Root")
			{
				int col = treeNode.Level-1;
				xlsFormater.AddCell(col, excelRow, treeNode.Text);
				xlsFormater.SetSizeOfCol(col, 2);
			}
			if ( treeNode.Nodes.Count != 0 ) //ya des enfants
			{	
				// on parcours les enfants
				foreach (TreeNode tn in treeNode.Nodes)
			   	{
					excelRow++;
					excelCol++;
			    	PutNodeToExcelInRecursive(tn, xlsFormater, treeNode.Level-1);
			   	}	
			}
			else // pas d'enfants alors on envoi vers excel
			{
				DataTable tbl = null;
				if ( treeNode.Level == TreeClassementProfondeur+1 && excelRow == TreeClassementProfondeur+1 )
				{
					// on va utiliser la variable TreeClassementProfondeur qui correspond a la profondeur de l'arbo pour asvoir a quelle colonne je commence a ecrire les entete
					// on ecrit les entetes
					tbl = null;
					if ( m_dtCSV != null ) 
						tbl = PopulateXPTableWithTreeClassementByTreeNode(treeNode, false);
					int idx = TreeClassementProfondeur+1;
					foreach( DataColumn col in tbl.Columns)
					{
						xlsFormater.AddCell(idx, 0, col.ColumnName);
						xlsFormater.AutoFitCols(idx,idx);
						idx++;
					}
				}
				//on tri la vue et on recup une table
				// on va remonter jusqu'au noeud le plus haut
				//TreeNode node = treeClassement.SelectedNode;
				tbl = null;
				if ( m_dtCSV != null ) 
					tbl = PopulateXPTableWithTreeClassementByTreeNode(treeNode, false);
				if ( tbl != null )
				{
					foreach(DataRow row in tbl.Rows)
					{
						excelRow++;
						int idx = TreeClassementProfondeur+1;
						int colCount = tbl.Columns.Count;
						for(int i=0;i<colCount;i++)
						{
							string str = Convert.ToString(row[i]);
							xlsFormater.AddCell(idx, excelRow, str);
							idx++;
						}
					}
				}
			}
		}
				
		void ExcelExporterBtnClick(object sender, EventArgs e)
		{
			ExportToExcel();
		}
		
		#endregion

		#region OptionsDialog
		
		void ToolStripButton3Click(object sender, EventArgs e)
		{
			OptionsDlg dlg = new OptionsDlg(this, OptionsParam);
			
			dlg.ShowDialog();
		}
		
		#endregion
	}
}
